package com.ipartek.pruebas.bbdd;

import java.io.IOException;
import java.sql.*;

;
// import java.sql.PreparedStatement;

/**
 * @author practicas10
 */

public class MostrarAlumnosP10 {

	static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
	static final String BBDD_NAME = "jdbc:odbc:pruebas";
	static final String BBDD_USER = "";
	static final String BBDD_PASS = "";

	static final String TABLE_USE = " ALUMNOS ";
	static final String FIELD_TABLE_ALUMNOS_NOMBRE = " NOMBRE ";
	static final String FIELD_TABLE_ALUMNOS_APELLIDO1 = " APELLIDO1 ";
	static final String FIELD_TABLE_ALUMNOS_APELLIDO2 = " APELLIDO2 ";
	static final String FIELD_TABLE_ALUMNOS_EDAD = " EDAD ";
	static final String FIELD_TABLE_ALUMNOS_EMAIL = " EMAIL ";
	static final String FIELD_TABLE_ALUMNOS_DNI = " DNI ";
	

	/**
	 * @param args
	 * @throws IOException
	 */
	public static void main(String[] args) throws IOException {
		/**
		 * Consultas SQL
		 */

		Connection conn = null;
		Statement stmt = null;
		

		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(BBDD_NAME, BBDD_USER, BBDD_PASS);
			stmt = conn.createStatement();
			stmt.executeQuery("SELECT" + FIELD_TABLE_ALUMNOS_NOMBRE + "FROM"
					+ TABLE_USE);
			ResultSet resultG = stmt.executeQuery("SELECT nombre FROM"
					+ TABLE_USE);
			System.out.println(resultG);

			ResultSet resultA = stmt.executeQuery("SELECT " + "*" + "FROM"
					+ TABLE_USE + "WHERE edad >= 20");
			int columns = resultA.getMetaData().getColumnCount();
			StringBuilder message = new StringBuilder();
			while (resultA.next()) {
				for (int i = 1; i <= columns; i++) {
					message.append(resultA.getString(i) + " ");
				}
				message.append("\n");
			}
			System.out.println(message); // print table contents

			ResultSet resultB = stmt.executeQuery("SELECT " + "*" + "FROM"
					+ TABLE_USE + "WHERE" + FIELD_TABLE_ALUMNOS_EDAD
					+ "BETWEEN 20 AND 30");
			int columnsB = resultB.getMetaData().getColumnCount();
			StringBuilder messageB = new StringBuilder();
			while (resultB.next()) {
				for (int i = 1; i <= columnsB; i++) {
					messageB.append(resultB.getString(i) + " ");
				}
				messageB.append("\n");
			}
			System.out.println(messageB); // print table contents

			ResultSet resultL = stmt.executeQuery("SELECT " + "*" + "FROM"
					+ TABLE_USE + "WHERE" + FIELD_TABLE_ALUMNOS_NOMBRE
					+ "LIKE '%fra%'OR '%ab%'");
			int columnsL = resultL.getMetaData().getColumnCount();
			StringBuilder messageL = new StringBuilder();
			while (resultL.next()) {
				for (int i = 1; i <= columnsL; i++) {
					messageL.append(resultL.getString(i) + " ");
				}
				messageL.append("\n");
			}
			System.out.println(messageL); // print table contents
			conn.close();
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("\n--- SQLException caught ---\n");
			while (ex != null) {
				System.out.println("Message: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("ErrorCode: " + ex.getErrorCode());
				ex = ex.getNextException();
				System.out.println("");
			}
		} catch (ClassNotFoundException e) {
			System.out.println("Excepcion de Clase no encontrada");
			e.printStackTrace();

		} catch (Exception e) {
			System.out.println("Excepcion General");
			e.printStackTrace();
		} finally {
			// conn.close();
			// stmt.close();
		}
	}
}
